﻿use master
go
create  database  Student
on
(
       name='student',
	   filename='D:\SQL\Student.mdf',
	   size=5MB,
	   maxsize=50MB,
	   filegrowth=10%
)
log on
(
       name='Student_log',
	   filename='D:\SQL\Student_log.ldf',
	   size=5MB,
	   maxsize=50MB,
	   filegrowth=10%
)
go

use Student
go

create table Class
(
ClassID int primary key identity(1,1),
ClassNam nvarchar(20) not null
)
go

create table Student
(
StuID int primary key identity(1,1),
ClassID int references Class(ClassID),
StuName nvarchar(20) not null,
StuSex nvarchar(1) check(StuSex='' or StuSex='Ů'),
StuBirthday date,
StuPhone nvarchar(11) unique null,
StuAddress nvarchar(200) null
)
go

create table Course
(
CourseID int primary key identity(1,1),
CourseName nvarchar(50) unique not null,
CourseCredit int default'1' check(1<=CourseCredit and CourseCredit<=5)
)
go

create table Score
(
ScoreID int  primary key identity(1,1),
StuID int,
CourseID int,
Score decimal(5,2)  unique not null
)
go

alter table Student add StuAddress int

alter table Score add constraint PK_Score_ScoreID primary key(ScoreID)
alter table Score add constraint FK_Score_Student foreign key(StuID) references Student(StuID)
alter table Score add constraint FK_Score_Course foreign key(CourseID) references Course(CourseID)



